package com.report.conversion.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 根据模板生成excel文件 工具类
 *
 * @author: kevin wu
 * @date: 2023年03月11日 9:38
 */
@Component
public class ExcelReportCore {

    /***
     * @description 根据模板 生成文件
     * @param sourceFileName 模板全路径 包含文件名
     * @param outFileName 生成文件全路径包含名称
     * @param paramMap   需要替换的参数 HashMap key 为 模板参数定义 value 为要替换的值
     * @return java.lang.String
     * @author kevin wu
     * @date 2023/3/13 15:03
     */
    public String buildByTemplate(String sourceFileName,String outFileName, Map<String, Object> paramMap) throws Exception{
        InputStream inputStream = new FileInputStream(new File(sourceFileName));
        return buildByTemplate(inputStream,outFileName, paramMap);
    }

    /***
     * @description 根据模板 生成文件
     * @param inputStream 模板输入流
     * @param outFileName 生成文件全路径包含名称
     * @param paramMap   需要替换的参数 HashMap key 为 模板参数定义 value 为要替换的值
     * @return java.lang.String
     * @author kevin wu
     * @date 2023/3/13 15:01
     */
    public  String buildByTemplate(InputStream inputStream,String outFileName, Map<String, Object> paramMap) throws Exception{
        Workbook workbook = ExcelReportCore.buildByTemplate(inputStream, paramMap);
        // 2.保存到本地
        save(workbook, outFileName);
        return outFileName;
    }

    /***
     * @description 根据模板输入流 和 要替换的参数 进行模板参数替换
     * @param inputStream 模板文件输入文件流
     * @param paramMap 需要替换的参数 HashMap key 为 模板参数定义 value 为要替换的值
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author kevin wu
     * @date 2023/3/11 9:46
     */
    public static Workbook buildByTemplate(InputStream inputStream, Map<String, Object> paramMap) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //循环处理所有的sheet
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            XSSFSheet sheetAt = workbook.getSheetAt(i);
            handleSheet(sheetAt,paramMap);
        }
        return workbook;
    }

    /***
     * @description 针对sheet进行替换
     * @param sheet
     * @param paramMap
     * @return void
     * @author kevin wu
     * @date 2023/3/11 10:31
     */
    private static void handleSheet(XSSFSheet sheet, Map<String, Object> paramMap) throws Exception{
        //非list的单元格处理
        Integer[] integers = singleHandleSheet(sheet, paramMap);
        //list的替换处理
        if(integers!=null){
            //处理循环逻辑
            listHandleSheet(sheet, paramMap,integers);
        }
    }

    /***
     * @description 非list的变量替换 返回list的开始行号数组
     * @param sheet
     * @param paramMap
     * @return Integer[]
     * @author kevin wu
     * @date 2023/3/11 11:22
     */
    private static Integer[] singleHandleSheet(XSSFSheet sheet, Map<String, Object> paramMap) throws Exception{
        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++){
            XSSFRow row = sheet.getRow(i);
            if(row==null){
                continue;
            }
            //校验第一个单元格是否是循环处理的，循环处理的仅记录开始行号，后面统一处理
            XSSFCell cell = row.getCell(0);
            String value = getCellVal(cell);
            if(value!=null && value.indexOf("List")>=0){
                if("".equals(str)){
                    str = ""+i;
                }else {
                    str = str+"_"+i;
                }
                continue;
            }else {
                rowReplace(row,paramMap);
            }
        }
        if(!"".equals(str)){
            if(str.contains("_")){
                 String[] s = str.split("_");
                //按照行号从大到小排序
                Integer[] arr= new Integer[s.length];
                for (int i = 0; i < s.length; i++) {
                    arr[i] = Integer.parseInt(s[i]);
                }
                Arrays.sort(arr, Collections.reverseOrder());
                 return arr;
            }else {
                return  new Integer[]{Integer.parseInt(str)};
            }
        }else {
            return null;
        }
    }

    /***
     * @description 替换list的变量替换以及行复制等
     * @param sheet
     * @param paramMap
     * @param lines
     * @return void
     * @author kevin wu
     * @date 2023/3/11 11:32
     */
    private static void listHandleSheet(XSSFSheet sheet, Map<String, Object> paramMap,Integer[] lines) throws Exception{
        //循环，先处理最后的行，后处理前面的行号
        for (Integer line : lines) {
            XSSFRow row = sheet.getRow(line);
            XSSFCell cell = row.getCell(0);
            String value = getCellVal(cell);
            String key = value.split("-")[0];
            List<HashMap> o = (List)paramMap.get(key);
            int rowIndex = line;
            int rows = o.size();
            int copyRows = rows - 1;
            if (copyRows > 0) {
                // shiftRows: 从startRow到最后一行，全部向下移copyRows行
                sheet.shiftRows(rowIndex, sheet.getLastRowNum(), copyRows, true, false);
                // 拷贝策略
                CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
                cellCopyPolicy.setCopyCellValue(true);
                cellCopyPolicy.setCopyCellStyle(true);
                // 这里模板row已经变成了startRow + copyRows,
                int templateRow = rowIndex + copyRows;
                // 因为下移了，所以要把模板row拷贝到所有空行
                for (int i = 0; i < copyRows; i++) {
                    sheet.copyRows(templateRow, templateRow, rowIndex + i, cellCopyPolicy);
                }
            }
            int k = 0;
            for (int j = rowIndex; j < rowIndex + rows; j++){
                HashMap hashMap = o.get(k);
                k++;
                rowReplace(sheet.getRow(j),hashMap);
            }

        }
    }

    /***
     * @description 单行内容替换
     * @param row
     * @param paramMap
     * @return void
     * @author kevin wu
     * @date 2023/3/11 14:24
     */
    private static void rowReplace(XSSFRow row, Map<String, Object> paramMap) throws Exception{
        //处理第一列
        XSSFCell cell = row.getCell(0);
        String value = getCellVal(cell);
        if(value.contains("-")){
            String tmpvalue = value.split("-")[1];
            cell.setCellValue(tmpvalue);
        }
        //循环当前行的所有列进行替换
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
            XSSFCell tmpcell = row.getCell(j);
            String cellValue = getCellVal(tmpcell);
            if(isEmpty(cellValue) || !cellValue.contains("}}")) {
                continue;
            }else{
                String[] split = cellValue.split("}}");
                if(split.length>1){
                    //存在多个变量
                    while (cellValue.contains("}}")){
                        int ii = cellValue.indexOf("{{");
                        int ii1 = cellValue.indexOf("}}");
                        String key = cellValue.substring(ii+2, ii1);
                        //paramMap中获取对应的参数
                        String rtx = "";
                        if(!isEmpty(paramMap.get(key))){
                            rtx = paramMap.get(key).toString();
                        }else {
                            rtx = "";
                        }
                        String rpl = "\\{\\{"+key+"}}";
                        cellValue = cellValue.replaceAll(rpl, rtx);
                    }
                    tmpcell.setCellValue(cellValue);
                }else {
                    //仅一个变量
                    int ii = cellValue.indexOf("{{");
                    int ii1 = cellValue.indexOf("}}");
                    String key = cellValue.substring(ii+2, ii1);
                    if(!isEmpty(paramMap.get(key))){
                        Object o = paramMap.get(key);
                        if(o instanceof Double){
                            tmpcell.setCellValue(new Double(o.toString()));
                        }else{
                            tmpcell.setCellValue(o.toString());
                        }
                    }else {
                        tmpcell.setCellValue("");
                    }
                }
            }
        }
    }


    /***
     * @description 根据单元不同的类型读取单元格的值转换成String
     * @param cell
     * @return java.lang.String
     */
    private static String getCellVal(XSSFCell cell) {

        String val = "";
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat df = new DecimalFormat("0.0000");
        if(cell==null){
            return val;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    val = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    val = df.format(cell.getNumericCellValue()); // 数字型
                    val = val.replaceAll("0+?$", "").replaceAll("[.]$", "");
                }
                break;
            case STRING:
                val = cell.getStringCellValue();
                break;
            case BOOLEAN:
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                val = cell.getStringCellValue();
                break;
            case ERROR:
                val = "";
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

    /***
     * @description 将替换的文件保存到磁盘
     * @param workbook  要保存的 excel对象
     * @param excelFilePath 保存文件的绝对路径+文件名
     * @return void
     * @author kevin wu
     * @date 2023/3/11 9:51
     */
    public static void save(Workbook workbook, String excelFilePath) throws IOException {
        String destDirName = excelFilePath.substring(0, excelFilePath.lastIndexOf(File.separator) + 1);
        File dir = new File(destDirName);
        if(!dir.exists()){
            dir.mkdirs();
        }
        FileOutputStream outputStream = new FileOutputStream(excelFilePath);
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        workbook.close();
    }

    /***
     * @description 在sheet中根据值获取单元格List
     * @param sheet
     * @param findCellValueList      要查找的值
     * @return java.util.List<org.apache.poi.ss.usermodel.Cell>
     */
    public static List<Cell> findCellList(Sheet sheet, List<String> findCellValueList) {
        List<Cell> cellList = new ArrayList<>();
        for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            List<Cell> rowCellList = findCellList(row, findCellValueList);
            if (!isEmpty(rowCellList)) {
                cellList.addAll(rowCellList);
            }
        }
        return cellList;
    }

    /***
     * @description 在行中根据值获取单元格List
     * @param row
     * @param findCellValueList
     * @return java.util.List<org.apache.poi.ss.usermodel.Cell>
     */
    public static List<Cell> findCellList(Row row, List<String> findCellValueList) {
        List<Cell> cellList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            String stringCellValue = row.getCell(j).getStringCellValue();
            if (findCellValueList.contains(stringCellValue)) {
                cellList.add(row.getCell(j));
            }
        }
        return cellList;
    }

    /***
     * @description 设置单元格字体样式
     * @param cellList
     * @param fontParam
     * @return void
     */
    public static void setCellFont(List<Cell> cellList, FontParams fontParam) {
        if (isEmpty(cellList) || fontParam == null) {
            return;
        }
        CellStyle cellStyle = null;
        for (Cell cell : cellList) {
            if (cellStyle == null) {
                Workbook workbook = cell.getSheet().getWorkbook();
                cellStyle = workbook.createCellStyle();
                // 从现有样式克隆style，只修改Font，其它style不变
                cellStyle.cloneStyleFrom(cell.getCellStyle());
                // 获取原有字体
                Font oldFont = workbook.getFontAt(cellStyle.getFontIndexAsInt());
                // 创建新字体
                Font newFont = workbook.createFont();
                newFont.setFontName(fontParam.getFontName() == null? oldFont.getFontName(): fontParam.getFontName());
                newFont.setFontHeightInPoints(fontParam.getFontHeightInPoints() == null? oldFont.getFontHeightInPoints(): fontParam.getFontHeightInPoints());
                newFont.setBold(fontParam.getBold() == null? oldFont.getBold(): fontParam.getBold());
                newFont.setItalic(fontParam.getItalic() == null? oldFont.getItalic(): fontParam.getItalic());
                newFont.setStrikeout(fontParam.getStrikeout() == null? oldFont.getStrikeout(): fontParam.getStrikeout());
                newFont.setUnderline(fontParam.getUnderline() == null? oldFont.getUnderline(): fontParam.getUnderline());
                newFont.setColor(fontParam.getColor() == null? oldFont.getColor(): fontParam.getColor());
                // 设置字体
                cellStyle.setFont(newFont);
            }
            // 设置样式
            cell.setCellStyle(cellStyle);
        }
    }

    /***
     * @description 隐藏列
     * @param sheet
     * @param hiddenColumn
     * @return void
     */
    public static void hiddenColumn(Sheet sheet, int hiddenColumn){
        sheet.setColumnHidden(hiddenColumn,true);
    }

    /***
     * @description 校验字符串是否为空
     * @param str
     * @return boolean
     */
    private static boolean isEmpty(Object str) {
        return (str == null || "".equals(str));
    }

}
